package com.sky.service.impl;

import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.WorkSpaceService;
import com.sky.service.WorkbookExportService;
import com.sky.vo.BusinessDataVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Slf4j
public class WorkbookExportServiceImpl implements WorkbookExportService {

    @Autowired
    private WorkSpaceService workspaceService;
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private UserMapper userMapper;

    /**
     * 导出运营数据报表
     *
     * @param response
     */
    public void exportBusinessData(HttpServletResponse response) {
        /*
        所需数据：
            1. 营业额（已完成订单的总金额）
            2. 有效订单（已完成订单的数量）
            3. 订单完成率（有效订单数/总订单数 * 100%）
            4. 平均客单价（营业额/有效订单数）
            5. 新增用户（新增用户的数量）
        概览数据：
            近三十天的数据
        明细数据：
            近三十天的每天数据
         */
        //1. 查询数据库，获取营业数据---查询最近30天的运营数据
        LocalDate dateBegin = LocalDate.now().minusDays(30);
        LocalDate dateEnd = LocalDate.now().minusDays(1);

        //查询概览数据
        BusinessDataVO businessDataVO = workspaceService.getBusinessData(LocalDateTime.of(dateBegin, LocalTime.MIN), LocalDateTime.of(dateEnd, LocalTime.MAX));

        //2. 通过POI将数据写入到Excel文件中
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");

        try {
            //基于模板文件创建一个新的Excel文件
            XSSFWorkbook excel = null;
            if (in != null) {
                excel = new XSSFWorkbook(in);
            }

            //获取表格文件的Sheet页
            XSSFSheet sheet = excel.getSheet("Sheet1");
            // 创建样式并设置居中
            CellStyle style = excel.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);      // 水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

            //填充数据--时间
            Cell cell = sheet.getRow(1).getCell(1);
            cell.setCellStyle(style);
            cell.setCellValue("时间：" + dateBegin + "至" + dateEnd);

            //获得第4行
            XSSFRow row = sheet.getRow(3);
            row.getCell(2).setCellValue(businessDataVO.getTurnover());
            row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());
            row.getCell(6).setCellValue(businessDataVO.getNewUsers());

            //获得第5行
            row = sheet.getRow(4);
            row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());
            row.getCell(4).setCellValue(businessDataVO.getUnitPrice());

            //填充明细数据
            for (int i = 0; i < 30; i++) {
                LocalDate date = dateBegin.plusDays(i);
                //查询某一天的营业数据
                BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));

                //获得某一行
                row = sheet.getRow(7 + i);
                row.getCell(1).setCellValue(date.toString());
                row.getCell(2).setCellValue(businessData.getTurnover());
                row.getCell(3).setCellValue(businessData.getValidOrderCount());
                row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
                row.getCell(5).setCellValue(businessData.getUnitPrice());
                row.getCell(6).setCellValue(businessData.getNewUsers());
            }

            //3. 通过输出流将Excel文件下载到客户端浏览器
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);

            //关闭资源
            out.close();
            excel.close();
        } catch (IOException e) {
            log.error("导出运营数据报表失败：{}", e.getMessage());
        }

    }

    /**
     * 获取订单数量（近三十天）
     *
     * @param status 订单状态
     * @return
     */
    public Integer orderCount(LocalDateTime beginTime, LocalDateTime endTime, Integer status) {
        Map<String, Object> map = new HashMap<>();
        map.put("beginTime", beginTime);
        map.put("endTime", endTime);
        map.put("status", status);
        return orderMapper.orderByOrderTime(map);
    }

    /**
     * 营业额通过状态查询（近三十天）
     *
     * @param status
     * @return
     */
    public Double sumOrderByStatus(LocalDateTime beginTime, LocalDateTime endTime, Integer status) {
        Map<String, Object> map = new HashMap<>();
        map.put("beginTime", beginTime);
        map.put("endTime", endTime);
        map.put("status", status);
        return orderMapper.sumByOrderTimeBetween(map);
    }

    /**
     * 获取数据(概览数据和明细数据)
     *
     * @param dateList                日期集合
     * @param turnoverList            营业额集合
     * @param validOrderCountList     有效订单数集合
     * @param orderCompletionRateList 订单完成率集合
     * @param unitPriceList           平均客单价集合
     * @param newUsersList            新增用户数集合
     */
    public void DataUtils(
            List<LocalDate> dateList,
            List<Double> turnoverList,
            List<Integer> validOrderCountList,
            List<String> orderCompletionRateList,
            List<Double> unitPriceList,
            List<Integer> newUsersList) {
        // 获取当日日期
        LocalDateTime beginTime = null;
        LocalDateTime endTime = LocalDateTime.of(LocalDate.now(), LocalTime.MAX);
        beginTime = endTime.minusDays(30);

        // 概览数据(近三十天)
        // 1. 营业额（已完成订单的总金额） -- 查询订单状态为已完成的金额合计
        Double turnoverMonth = sumOrderByStatus(beginTime, endTime, Orders.COMPLETED);
        turnoverList.add(turnoverMonth);
        // 2. 有效订单（已完成订单的数量）
        Integer validOrderCountMonth = orderCount(beginTime, endTime, Orders.COMPLETED);
        validOrderCountList.add(validOrderCountMonth);
        // 3. 订单完成率（有效订单数/总订单数 * 100%）
        // 总订单数（所有订单的数量）
        Integer orderCountMonth = orderCount(beginTime, endTime, null);
        // 订单完成率
        double orderCompletionRateMonth = 0.0;
        if (orderCountMonth != 0) {
            orderCompletionRateMonth = validOrderCountMonth.doubleValue() / orderCountMonth.doubleValue() * 100;
        }
        String rateMonth = orderCompletionRateMonth + "%";
        orderCompletionRateList.add(rateMonth);
        // 4. 平均客单价（营业额/有效订单数）
        double unitPriceMonth = 0.0;
        if (validOrderCountMonth != 0) {
            unitPriceMonth = turnoverMonth / validOrderCountMonth;
        }
        unitPriceList.add(unitPriceMonth);
        // 5. 新增用户（新增用户的数量）
        Integer newUsersMonth = userMapper.getUserCountForTime(beginTime, endTime);
        newUsersList.add(newUsersMonth);

        // 明细数据
        // 1. 日期
        int count = 0;
        dateList.add(endTime.toLocalDate());
        while (count < 30) {
            beginTime = endTime.minusDays(1);
            dateList.add(beginTime.toLocalDate());
            count++;
        }

        for (LocalDate date : dateList) {
            beginTime = LocalDateTime.of(date, LocalTime.MIN);
            endTime = LocalDateTime.of(date, LocalTime.MAX);
            // 2. 营业额
            Double turnover = sumOrderByStatus(beginTime, endTime, Orders.COMPLETED);
            turnoverList.add(turnover);
            // 3. 有效订单数
            Integer validOrderCount = orderCount(beginTime, endTime, Orders.COMPLETED);
            validOrderCountList.add(validOrderCount);
            // 4. 订单完成率
            // 总订单数（所有订单的数量）
            Integer orderCount = orderCount(beginTime, endTime, null);
            // 订单完成率
            double orderCompletionRate = 0.0;
            if (orderCount != 0) {
                orderCompletionRate = validOrderCount.doubleValue() / orderCount.doubleValue() * 100;
            }
            String rate = orderCompletionRate + "%";
            orderCompletionRateList.add(rate);
            // 5. 平均客单价
            double unitPrice = 0.0;
            if (validOrderCount != 0) {
                unitPrice = turnover / validOrderCount;
            }
            unitPriceList.add(unitPrice);
            // 6. 新增用户数
            Integer newUsers = userMapper.getUserCountForTime(beginTime, endTime);
            newUsersList.add(newUsers);
        }
    }
}
